package com.opennews.openplatform.familyexpenseapi.service

import com.opennews.openplatform.familyexpenseapi.common.constant.IncomeExpenseType
import com.opennews.openplatform.familyexpenseapi.common.constant.PaymentType
import com.opennews.openplatform.familyexpenseapi.dto.income.expense.detail.IncomeExpenseDetailQueryArgument
import com.opennews.openplatform.familyexpenseapi.dto.journey.income.expense.detail.BaseJourneyIncomeExpenseDetailDto
import com.opennews.openplatform.familyexpenseapi.dto.journey.income.expense.detail.CreateJourneyIncomeExpenseDetailDto
import com.opennews.openplatform.familyexpenseapi.dto.journey.income.expense.detail.UpdateJourneyIncomeExpenseDetailDto
import com.opennews.openplatform.familyexpenseapi.entity.Tables.*
import com.opennews.openplatform.familyexpenseapi.entity.tables.records.JourneyIncomeExpenseDetailRecord
import com.opennews.openplatform.familyexpenseapi.jooq.DSLContextWrapper
import com.opennews.openplatform.myspringbootcore.extension.isNullOrBlankOrLiteralNull
import jakarta.validation.Valid
import org.jooq.DSLContext
import org.jooq.impl.DSL
import org.springframework.stereotype.Service
import java.time.LocalDateTime

@Service
class JourneyIncomeExpenseDetailService(
    private val dslContext: DSLContext,
    private val dslContextWrapper: DSLContextWrapper,
    private val bankService: BankService,
    private val userService: UserService,
) {
    /**
     * Queries all journey income/expense associated with the provided account group.
     *
     * @return A list of map instances containing the keys below:
     * id: String of the income expense detail id.
     * location: String of the income or expense location.
     * currency: String of the income or expense currency.
     * amount: Float of the income expense detail amount.
     * type: String of the type. It's income or expense.
     * paymentType: String of the payment type which how the expense is paid.
     * bankId: String of the bank id of the bank if the expense is paid by card.
     * bankShortName: String of the short name of the bank if the expense is paid by card.
     * bankLogoUrl: String of the logo URL of the bank if the expense is paid by card.
     * bankCardId: String of the id of the bank card if the expense is paid by card.
     * bankCardName: String of the name of the bank card if the expense is paid by card.
     * bankCardIsCreditCard: Boolean if this bank card is a credit card.
     * details: String of the income or expense detail.
     */
    fun queryListByJourney(
        journeyId: String?,
        queryArgument: IncomeExpenseDetailQueryArgument,
    ): List<Map<String, Any>> {
        // Projections
        val select = dslContext
            .select(
                JOURNEY_INCOME_EXPENSE_DETAIL.ID.`as`("id"),
                JOURNEY_INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.`as`("incomeExpenseDate"),
                JOURNEY_INCOME_EXPENSE_DETAIL.LOCATION.`as`("location"),
                JOURNEY_INCOME_EXPENSE_DETAIL.CURRENCY.`as`("currency"),
                JOURNEY_INCOME_EXPENSE_DETAIL.AMOUNT.`as`("amount"),
                JOURNEY_INCOME_EXPENSE_DETAIL.TYPE.`as`("type"),
                JOURNEY_INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.`as`("paymentType"),
                BANK.ID.`as`("bankId"),
                BANK.SHORT_NAME.`as`("bankShortName"),
                BANK_CARD.ID.`as`("bankCardId"),
                BANK_CARD.NAME.`as`("bankCardName"),
                BANK_CARD.IS_CREDIT_CARD.`as`("bankCardIsCreditCard"),
                JOURNEY_INCOME_EXPENSE_DETAIL.DETAILS.`as`("details"),
                if (!queryArgument.keyword.isNullOrBlankOrLiteralNull()) {
                    JOURNEY.ID
                } else {
                    DSL.`val`(null as String?)
                }.`as`("journeyId"),
                if (!queryArgument.keyword.isNullOrBlankOrLiteralNull()) {
                    JOURNEY.START_DATE
                } else {
                    DSL.`val`(null as LocalDateTime?)
                }.`as`("journeyStartDate"),
                if (!queryArgument.keyword.isNullOrBlankOrLiteralNull()) {
                    JOURNEY.END_DATE
                } else {
                    DSL.`val`(null as LocalDateTime?)
                }.`as`("journeyEndDate"),
                if (!queryArgument.keyword.isNullOrBlankOrLiteralNull()) {
                    REGION.TITLE
                } else {
                    DSL.`val`(null as String?)
                }.`as`("regionTitle"),
            )

        // Queries detail by account group id.
        val query = select.from(JOURNEY_INCOME_EXPENSE_DETAIL)
            .leftJoin(BANK_CARD).on(JOURNEY_INCOME_EXPENSE_DETAIL.BANK_CARD_ID.eq(BANK_CARD.ID))
            .leftJoin(BANK).on(BANK_CARD.BANK_ID.eq(BANK.ID))

        if (!queryArgument.keyword.isNullOrBlankOrLiteralNull()) {
            // Sets the join type for journey property in order to get its region.
            // Sets the join type for region property in order to get its title.
            query.join(JOURNEY).on(JOURNEY_INCOME_EXPENSE_DETAIL.JOURNEY_ID.eq(JOURNEY.ID))
                .join(REGION).on(JOURNEY.REGION_ID.eq(REGION.ID))
        }

        query.where(
            JOURNEY_INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(queryArgument.accountGroupId)
        )

        if (!journeyId.isNullOrBlankOrLiteralNull()) {
            query.and(JOURNEY_INCOME_EXPENSE_DETAIL.JOURNEY_ID.eq(journeyId))
        }

        if (!queryArgument.keyword.isNullOrBlankOrLiteralNull()) {
            // When searching with AND condition for multiple keywords then we just need to wrap each keyword into one OR section.
            // The keyword supports separated by blank string.
            // When it occurs we are splitting the keyword with blank string and gets multiple keywords.
            // The multiple keywords will be used to search within OR condition.
            // Regarding the dynamic property in criteria please check on https://stackoverflow.com/questions/11550507/build-createcriteria-in-grails-dynamically-and-in-a-dry-way
            queryArgument.keyword?.split("\\s+".toRegex())?.let { keywords ->
                var keywordCondition = DSL.noCondition()

                for (key in keywords) {
                    keywordCondition = keywordCondition.and(JOURNEY_INCOME_EXPENSE_DETAIL.DETAILS.like("%$key%"))
                        .or(JOURNEY_INCOME_EXPENSE_DETAIL.REMARK.like("%$key%"))
                }

                query.and(keywordCondition)
            }
        }

        query.orderBy(
            JOURNEY_INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.desc(),
            JOURNEY_INCOME_EXPENSE_DETAIL.TYPE.desc(),
            BANK_CARD.ID.asc(),
            JOURNEY_INCOME_EXPENSE_DETAIL.LAST_UPDATED.desc()
        )

        queryArgument.pageSize?.run {
            // How many rows are returned for this trip.
            query.limit(this)
        }

        queryArgument.pageSize?.let { size ->
            queryArgument.pageIndex?.let { index ->
                // Where to take the rows.
                query.offset(size * index)
            }
        }

        return query.fetch()
            .map { p ->
                val item = p.intoMap()

                // Adds bankLogoUrl key to the map item.
                item["bankLogoUrl"] = bankService.getLogoUrl(item["bankShortName"] as String?)

                item
            }
    }

    /**
     * Queries journey income expense details by its id.
     *
     * @param accountGroupId The string of user account group id.
     * @param id The string of the income expense detail id.
     * @return The map instance which contains keys below.
     * id: String of the income expense detail id.
     * journeyId: String of the journey id.
     * location: String of the income or expense location.
     * currency: String of the income or expense currency.
     * amount: Float of the income expense detail amount.
     * type: String of the type. It's income or expense.
     * paymentType: String of the payment type which how the expense payed.
     * bankId: String of the band id of the bank card if the expense is payed by card.
     * bankShortName: String of the short name of the bank card if the expense is payed by card.
     * bankCardId: String of the id of the bank card if the expense is payed by card.
     * bankCardName: String of the name of the bank card if the expense is payed by card.
     * details: String of the income or expense detail.
     * remark: String of the necessary extra remark.
     * createdBy: String of username of who created this item.
     * updatedBy: String of username of who updated this item.
     * dateCreated: Date of when this detail created.
     * lastUpdated: Date of when this detail updated.
     */
    fun queryDetails(accountGroupId: String, id: String): Map<String, Any> {
        // Queries detail by account group id.
        val result = dslContext
            .select(
                JOURNEY_INCOME_EXPENSE_DETAIL.ID.`as`("id"),
                JOURNEY_INCOME_EXPENSE_DETAIL.JOURNEY_ID.`as`("journeyId"),
                JOURNEY_INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.`as`("incomeExpenseDate"),
                JOURNEY_INCOME_EXPENSE_DETAIL.LOCATION.`as`("location"),
                JOURNEY_INCOME_EXPENSE_DETAIL.CURRENCY.`as`("currency"),
                JOURNEY_INCOME_EXPENSE_DETAIL.AMOUNT.`as`("amount"),
                JOURNEY_INCOME_EXPENSE_DETAIL.TYPE.`as`("type"),
                JOURNEY_INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.`as`("paymentType"),
                BANK.ID.`as`("bankId"),
                BANK.SHORT_NAME.`as`("bankShortName"),
                BANK_CARD.ID.`as`("bankCardId"),
                BANK_CARD.NAME.`as`("bankCardName"),
                JOURNEY_INCOME_EXPENSE_DETAIL.DETAILS.`as`("details"),
                JOURNEY_INCOME_EXPENSE_DETAIL.REMARK.`as`("remark"),
                JOURNEY_INCOME_EXPENSE_DETAIL.LATITUDE.`as`("latitude"),
                JOURNEY_INCOME_EXPENSE_DETAIL.LONGITUDE.`as`("longitude"),
                JOURNEY_INCOME_EXPENSE_DETAIL.ADDRESS.`as`("address"),
                JOURNEY_INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS.`as`("formattedAddress"),
                JOURNEY_INCOME_EXPENSE_DETAIL.PROVINCE.`as`("province"),
                JOURNEY_INCOME_EXPENSE_DETAIL.CITY.`as`("city"),
                JOURNEY_INCOME_EXPENSE_DETAIL.DISTRICT.`as`("district"),
                JOURNEY_INCOME_EXPENSE_DETAIL.CREATED_BY.`as`("createdBy"),
                JOURNEY_INCOME_EXPENSE_DETAIL.UPDATED_BY.`as`("updatedBy"),
                JOURNEY_INCOME_EXPENSE_DETAIL.DATE_CREATED.`as`("dateCreated"),
                JOURNEY_INCOME_EXPENSE_DETAIL.LAST_UPDATED.`as`("lastUpdated"),
            )
            .from(JOURNEY_INCOME_EXPENSE_DETAIL)
            .leftJoin(BANK_CARD).on(JOURNEY_INCOME_EXPENSE_DETAIL.BANK_CARD_ID.eq(BANK_CARD.ID))
            .leftJoin(BANK).on(BANK_CARD.BANK_ID.eq(BANK.ID))
            .where(
                JOURNEY_INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(JOURNEY_INCOME_EXPENSE_DETAIL.ID.eq(id))
            )
            .fetchOneMap()

        if (result == null) {
            return emptyMap()
        }

        // Queries createdBy & updatedBy basic info.
        val userDetails = userService.queryBasicInfo(
            accountGroupId,
            listOf(result["createdBy"].toString(), result["updatedBy"].toString())
        )

        result["createdBy"] = userDetails.first { it["username"] == result["createdBy"] }
        result["updatedBy"] = userDetails.first { it["username"] == result["updatedBy"] }

        // Sets the bank logo url based on its short name.
        result["bankLogoUrl"] = bankService.getLogoUrl(result["bankShortName"] as String?)

        return result
    }

    /**
     * Queries all distinct income/expense years.
     *
     * @param accountGroupId: The string of the user account group id.
     * @return The list of Integer of existing year.
     */
    fun queryExistingDistinctYears(accountGroupId: String): List<Int> {
        return dslContext.selectDistinct(DSL.year(JOURNEY_INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).`as`("year"))
            .from(JOURNEY_INCOME_EXPENSE_DETAIL)
            .where(JOURNEY_INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
            .orderBy(DSL.year(JOURNEY_INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).desc())
            .fetch()
            .getValues("year", Int::class.java)
    }

    /**
     * Queries coordinates for given account group id and year.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param year: The year to filter by.
     * @return A list of maps containing location details.
     */
    fun queryCoordinates(accountGroupId: String, year: String?): List<Map<String, Any>> {
        // Locations contain same formatted address but different coordinates, so only one match is needed.
        // https://www.navicat.com/en/company/aboutus/blog/1647-applying-select-distinct-to-one-column-only
        val subquery = dslContext.select(DSL.max(JOURNEY_INCOME_EXPENSE_DETAIL.ID))
            .from(JOURNEY_INCOME_EXPENSE_DETAIL)
            .where(
                JOURNEY_INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(JOURNEY_INCOME_EXPENSE_DETAIL.TYPE.eq(IncomeExpenseType.EXPENSE))
                    .and(JOURNEY_INCOME_EXPENSE_DETAIL.LATITUDE.isNotNull)
                    .and(JOURNEY_INCOME_EXPENSE_DETAIL.LONGITUDE.isNotNull)
                    .and(JOURNEY_INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS.isNotNull)
                    .and(JOURNEY_INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS.ne(""))
                    .and(
                        if (year.isNullOrBlankOrLiteralNull()) {
                            DSL.trueCondition()
                        } else {
                            DSL.year(JOURNEY_INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(year!!.toInt())
                        }
                    )
            )
            .groupBy(JOURNEY_INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS)

        return dslContext.select(
            JOURNEY_INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS.`as`("formattedAddress"),
            JOURNEY_INCOME_EXPENSE_DETAIL.LATITUDE.`as`("latitude"),
            JOURNEY_INCOME_EXPENSE_DETAIL.LONGITUDE.`as`("longitude")
        )
            .from(JOURNEY_INCOME_EXPENSE_DETAIL)
            .where(JOURNEY_INCOME_EXPENSE_DETAIL.ID.`in`(subquery))
            .fetchMaps()
    }

    /**
     * Adds a new record of JourneyIncomeExpenseDetail with posted data.
     * The username used to query the account group id and saved to createdBy and updatedBy.
     *
     * @param data: The CreateJourneyIncomeExpenseDetailDto instance holds data.
     * @param accountGroupId: The string of user account group id.
     * @param username: The string of username which is saved to createdBy and updatedBy.
     */
    fun add(accountGroupId: String, username: String, @Valid data: CreateJourneyIncomeExpenseDetailDto) {
        // Creates new JourneyIncomeExpenseDetail.
        createRecord(data, username).apply {
            // Associates with account group.
            this.accountGroupId = accountGroupId

            // Associates with journey.
            this.journeyId = data.journey?.id?.asString()

            // Specifies creator.
            this.createdBy = username

            // Saves change.
            insert()
        }
    }

    /**
     * Updates existing JourneyIncomeExpenseDetail with posted data.
     *
     * @param accountGroupId: The string of user account group id.
     * @param username: The string of username which saved to updatedBy.
     * @param data: The map instance which contains the keys with same names as JourneyIncomeExpenseDetail properties.
     */
    fun update(accountGroupId: String, username: String, @Valid data: UpdateJourneyIncomeExpenseDetailDto) {
        // Just makes sure only updates owned data.
        if (dslContextWrapper.exists(
                JOURNEY_INCOME_EXPENSE_DETAIL, JOURNEY_INCOME_EXPENSE_DETAIL.ID.eq(data.id)
                    .and(JOURNEY_INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
            )
        ) {
            val record = createRecord(data, username)
            record.changed(INCOME_EXPENSE_DETAIL.ID, false)

            // Specifies updater.
            record.updatedBy = username

            record.update()
        }
    }

    /**
     * Deletes the JourneyIncomeExpenseDetail with provided list of id.
     *
     * @param accountGroupId: The string of user account group id.
     * @param ids: The string list of id.
     */
    fun delete(accountGroupId: String, ids: List<String>) {
        if (ids.isNotEmpty()) {
            // Executes batch deleting by DetachedCriteria.
            dslContext.deleteFrom(JOURNEY_INCOME_EXPENSE_DETAIL)
                .where(
                    JOURNEY_INCOME_EXPENSE_DETAIL.ID.`in`(ids)
                        .and(JOURNEY_INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
                )
                .execute()
        }
    }

    /**
     * Creates JourneyIncomeExpenseDetailRecord based on BaseJourneyIncomeExpenseDetailDto.
     *
     * @param data:     The map instance which contains the keys with same names as IncomeExpenseDetail properties.
     * @param username: The string of username which saved to updatedBy.
     */
    private fun createRecord(
        @Valid data: BaseJourneyIncomeExpenseDetailDto,
        username: String,
    ): JourneyIncomeExpenseDetailRecord {
        val record = dslContext.newRecord(JOURNEY_INCOME_EXPENSE_DETAIL)

        // Updates the bank card property.
        record.bankCardId = data.bankCard?.id?.asString()

        // Updates all properties which data contains.
        record.from(data)

        // Specifies updater.
        record.updatedBy = username

        // Sets the default payment type if needed.
        if (record.paymentType.isNullOrBlankOrLiteralNull()) {
            record.paymentType = PaymentType.CASH
        }

        // Just in case user inputs negative number for income and positive number for expense.
        // We take the absolute value first.
        record.amount = record.amount.abs()

        // If it is expense we take it as negative number.
        if (record.type == IncomeExpenseType.EXPENSE) {
            record.amount = record.amount.negate()
        }

        return record
    }
}